/**
 * 
 */
package org.gov.vis.data.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.gov.vis.data.dto.FollowUpDetailsDto;
import org.gov.vis.data.dto.ProgencyDto;
import org.gov.vis.util.DBConstants;
import org.gov.vis.util.DBStatements;
import org.gov.vis.util.DU;
import org.gov.vis.util.Util;

/**
 * @author Vishwanath
 *
 */
public class ProgencyDaoImpl implements ProgencyDao{

	/**
	 *Method called to either Add or Edit a Progency
	 */
	@Override
	public void addOrEditProgency(final ProgencyDto _progencyDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		PreparedStatement pStmtAdd = null;
		PreparedStatement pStmtEdit = null;
		PreparedStatement pStmtSel = null;
		ResultSet rstSet = null;

		try {
			conn = Util.getConnection();
			conn.setAutoCommit(false);
			pStmtAdd = conn.prepareStatement(DBStatements.ADD_PRGYFOLLWUP);
			pStmtEdit = conn.prepareStatement(DBStatements.EDIT_PRGYFOLLWUP);
			pStmtSel = conn.prepareStatement(DBStatements.SELECT_MAX_PROGENCYID);

			if(_progencyDto.getPrgyId() > 0){
				pStmt = conn.prepareStatement(DBStatements.EDIT_PROGENCY);
				pStmt.setString(1, _progencyDto.getCode());
				pStmt.setString(2, _progencyDto.getFarmerCode());
				pStmt.setString(3, _progencyDto.getFarmerName());
				pStmt.setString(4, _progencyDto.getRelationShipName());
				pStmt.setString(5, _progencyDto.getVillage());
				pStmt.setString(6, _progencyDto.getMandal());
				pStmt.setString(7, _progencyDto.getDistrict());
				pStmt.setString(8, DU.format(_progencyDto.getClfDOB(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setString(9, _progencyDto.getBreedType());
				pStmt.setDouble(10, Double.parseDouble(_progencyDto.getPercentage()));
				pStmt.setInt(11, Integer.valueOf(_progencyDto.getSex()));
				pStmt.setString(12, _progencyDto.getColor());				
				pStmt.setString(13, _progencyDto.getTagNo());
				pStmt.setString(14, _progencyDto.getBreed());
				pStmt.setInt(15, Integer.valueOf(_progencyDto.getNoOfCalving()));
				pStmt.setInt(16, Integer.valueOf(_progencyDto.getNoOfAI()));
				pStmt.setDouble(17, Double.parseDouble(_progencyDto.getPreviousYield()));
				pStmt.setDouble(18, Double.parseDouble(_progencyDto.getPresentYield()));
				pStmt.setString(19, _progencyDto.getBullNo());
				//pStmt.setString(20, _progencyDto.getBirthWeight());
				//pStmt.setString(21, _progencyDto.getMonthlyWeight());
				pStmt.setDouble(20, Double.parseDouble(_progencyDto.getDmMilkYield()));
				pStmt.setString(21, _progencyDto.getSirenBreed());
				//pStmt.setString(23, _progencyDto.getOthers());
				//pStmt.setString(24, _progencyDto.getNutrition());
				pStmt.setLong(22, _progencyDto.getPrgyId());
			}
			else{
				pStmt = conn.prepareStatement(DBStatements.ADD_PROGENCY);
				pStmt.setString(1, _progencyDto.getCode());
				pStmt.setString(2, _progencyDto.getFarmerCode());
				pStmt.setString(3, _progencyDto.getFarmerName());
				pStmt.setString(4, _progencyDto.getRelationShipName());
				pStmt.setString(5, _progencyDto.getVillage());
				pStmt.setString(6, _progencyDto.getMandal());
				pStmt.setString(7, _progencyDto.getDistrict());
				pStmt.setString(8, DU.format(_progencyDto.getClfDOB(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setString(9, _progencyDto.getBreedType());
				pStmt.setDouble(10, Double.parseDouble(_progencyDto.getPercentage()));
				pStmt.setInt(11, Integer.valueOf(_progencyDto.getSex()));
				pStmt.setString(12, _progencyDto.getColor());				
				pStmt.setString(13, _progencyDto.getTagNo());
				pStmt.setString(14, _progencyDto.getBreed());
				pStmt.setInt(15, Integer.valueOf(_progencyDto.getNoOfCalving()));
				pStmt.setInt(16, Integer.valueOf(_progencyDto.getNoOfAI()));
				pStmt.setDouble(17, Double.parseDouble(_progencyDto.getPreviousYield()));
				pStmt.setDouble(18, Double.parseDouble(_progencyDto.getPresentYield()));
				pStmt.setString(19, _progencyDto.getBullNo());
				//pStmt.setString(20, _progencyDto.getBirthWeight());
				//pStmt.setString(21, _progencyDto.getMonthlyWeight());
				pStmt.setDouble(20, Double.parseDouble(_progencyDto.getDmMilkYield()));
				pStmt.setString(21, _progencyDto.getSirenBreed());
				//pStmt.setString(23, _progencyDto.getOthers());
				//pStmt.setString(24, _progencyDto.getNutrition());

				rstSet = pStmtSel.executeQuery();

				if(rstSet.next()){
					_progencyDto.setPrgyId(rstSet.getLong(1)+1);
				}				
			}

			//Method called to either Add or Edit the Progency FollowUp Details
			addOrEditProgencyFollowUp(_progencyDto, pStmtAdd, pStmtEdit);

			pStmt.executeUpdate();
			pStmtAdd.executeBatch();
			pStmtEdit.executeBatch();			
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			pStmt.close();
			pStmtAdd.close();
			pStmtEdit.close();
			pStmtSel.close();
			if(rstSet != null){
				rstSet.close();
			}
			conn.close();
		}
	}//end addOrEditDept

	/**
	 * Method called to either Add or Edit the Progency FollowUp Details
	 * @throws Exception
	 */
	private void addOrEditProgencyFollowUp(final ProgencyDto _progencyDto, final PreparedStatement _pStmtAdd, final PreparedStatement _pStmtEdit) throws Exception{
		//Add or Edit the FollowUp Details of DeWormings, if any
		if(_progencyDto.getFollowUpDetails() != null && !_progencyDto.getFollowUpDetails().isEmpty()){
			for(FollowUpDetailsDto prgyFollowUpDto : _progencyDto.getFollowUpDetails()){
				if(prgyFollowUpDto.getFollowUpId() > 0){
					_pStmtEdit.setString(1, DU.format(prgyFollowUpDto.getDeWormingDate(), DBConstants.MYSQL_DATE_FORMAT));
					_pStmtEdit.setString(2, DU.format(prgyFollowUpDto.getVaccinationDate(), DBConstants.MYSQL_DATE_FORMAT));
					_pStmtEdit.setString(3, DU.format(prgyFollowUpDto.getDeTickingDate(), DBConstants.MYSQL_DATE_FORMAT));
					_pStmtEdit.setString(4, prgyFollowUpDto.getOther());
					_pStmtEdit.setString(5, prgyFollowUpDto.getNutrition());
					_pStmtEdit.setString(6, prgyFollowUpDto.getBirthWeight());
					_pStmtEdit.setString(7, prgyFollowUpDto.getMonthlyWeight());
					_pStmtEdit.setLong(8, _progencyDto.getPrgyId());
					_pStmtEdit.setLong(9, prgyFollowUpDto.getFollowUpId());
					_pStmtEdit.addBatch();
				}
				else{
					_pStmtAdd.setString(1, DU.format(prgyFollowUpDto.getDeWormingDate(), DBConstants.MYSQL_DATE_FORMAT));
					_pStmtAdd.setString(2, DU.format(prgyFollowUpDto.getVaccinationDate(), DBConstants.MYSQL_DATE_FORMAT));
					_pStmtAdd.setString(3, DU.format(prgyFollowUpDto.getDeTickingDate(), DBConstants.MYSQL_DATE_FORMAT));
					_pStmtAdd.setString(4, prgyFollowUpDto.getOther());
					_pStmtAdd.setString(5, prgyFollowUpDto.getNutrition());
					_pStmtAdd.setString(6, prgyFollowUpDto.getBirthWeight());
					_pStmtAdd.setString(7, prgyFollowUpDto.getMonthlyWeight());
					_pStmtAdd.setLong(8, _progencyDto.getPrgyId());
					_pStmtAdd.addBatch();
				}
			}//end for
		}//end outer if		
	}//end addOrEditProgencyFollowUp

	/**
	 *Method called to either Add or Edit a Progency
	 */
	@Override
	public void addOrEditFollowUpProgency(final FollowUpDetailsDto _followUpProgencyDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmtAdd = null;
		PreparedStatement pStmtEdit = null;
		ResultSet rstSet = null;

		try {
			conn = Util.getConnection();
			conn.setAutoCommit(false);
			pStmtAdd = conn.prepareStatement(DBStatements.ADD_PRGYFOLLWUP);
			pStmtEdit = conn.prepareStatement(DBStatements.EDIT_PRGYFOLLWUP);

			ProgencyDto progencyDto = new ProgencyDto();
			progencyDto.setFollowUpDetails(new ArrayList<FollowUpDetailsDto>(1));
			progencyDto.getFollowUpDetails().add(_followUpProgencyDto);

			//Method called to either Add or Edit the Progency FollowUp Details
			addOrEditProgencyFollowUp(progencyDto, pStmtAdd, pStmtEdit);

			pStmtAdd.executeBatch();
			pStmtEdit.executeBatch();			
			conn.commit();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmtAdd.close();
			pStmtEdit.close();
			if(rstSet != null){
				rstSet.close();
			}
			conn.close();
		}
	}//end addOrEditDept

	/**
	 *Method called to Delete a Progency
	 */
	@Override
	public void deleteProgency(final long _progencyId) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.DELETE_PROGENCY);
			pStmt.setLong(1, _progencyId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

	}//end deleteDept

	/**
	 *Method called to get the list of Progencies based on the passed Progency 
	 */
	@Override
	public List<ProgencyDto> getProgencies(final ProgencyDto _progencyDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<ProgencyDto> _progencyList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_PROGENCY);
			if(_progencyDto.getPrgyId() > 0){
				pStmt.setLong(1, _progencyDto.getPrgyId());
				pStmt.setLong(2, _progencyDto.getPrgyId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}

			if(_progencyDto.getCode() != null && !_progencyDto.getCode().trim().equals("")){
				pStmt.setString(3, _progencyDto.getCode());
			}
			else{
				pStmt.setString(3, "%");
			}

			if(_progencyDto.getFarmerCode() != null && !_progencyDto.getFarmerCode().trim().equals("")){
				pStmt.setString(4, _progencyDto.getFarmerCode());
			}
			else{
				pStmt.setString(4,"%");
			}

			if(_progencyDto.getFarmerName() != null && !_progencyDto.getFarmerName().trim().equals("")){
				pStmt.setString(5, _progencyDto.getFarmerName());
			}
			else{
				pStmt.setString(5,"%");
			}

			if(_progencyDto.getBreedType() != null && !_progencyDto.getBreedType().trim().equals("")){
				pStmt.setString(6, _progencyDto.getBreedType());
			}
			else{
				pStmt.setString(6,"%");
			}

			if(_progencyDto.getBullNo() != null && !_progencyDto.getBullNo().trim().equals("")){
				pStmt.setString(7, _progencyDto.getBullNo());
			}
			else{
				pStmt.setString(7,"%");
			}

			rsSet = pStmt.executeQuery();

			_progencyList = populateProgencies(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _progencyList;
	}//end getProgencies


	/**
	 * Method called to populate the Progency records based on the passed result set
	 * @param _rsSet
	 * @return
	 */
	private List<ProgencyDto> populateProgencies(final ResultSet _rsSet)throws Exception{
		List<ProgencyDto> _progencyList = null;
		ProgencyDto _progencyDto = null;

		while(_rsSet.next()){
			if(_rsSet.isFirst()){
				_progencyList = new ArrayList<ProgencyDto>();
			}
			//initialize the Progency
			_progencyDto = new ProgencyDto();
			_progencyDto.setPrgyId(_rsSet.getLong(DBConstants.COL_PROGENCY_ID));
			_progencyDto.setCode(_rsSet.getString(DBConstants.COL_PROGENCY_CODE));
			_progencyDto.setFarmerCode(_rsSet.getString(DBConstants.COL_PROGENCY_FARMER_CODE));
			_progencyDto.setFarmerName(_rsSet.getString(DBConstants.COL_PROGENCY_FARMER_NAME));
			_progencyDto.setRelationShipName(_rsSet.getString(DBConstants.COL_PROGENCY_RELATIONSHIP_NAME));
			_progencyDto.setVillage(_rsSet.getString(DBConstants.COL_PROGENCY_VILLAGE));
			_progencyDto.setMandal(_rsSet.getString(DBConstants.COL_PROGENCY_MANDAL));
			_progencyDto.setDistrict(_rsSet.getString(DBConstants.COL_PROGENCY_DISTRICT));
			_progencyDto.setClfDOB(_rsSet.getDate(DBConstants.COL_PROGENCY_CALF_DOB));
			_progencyDto.setBreedType(_rsSet.getString(DBConstants.COL_PROGENCY_BREED_TYPE));
			_progencyDto.setPercentage(_rsSet.getString(DBConstants.COL_PROGENCY_PERCENTAGE));
			_progencyDto.setSex(_rsSet.getString(DBConstants.COL_PROGENCY_SEX));
			_progencyDto.setColor(_rsSet.getString(DBConstants.COL_PROGENCY_COLOR));
			_progencyDto.setTagNo(_rsSet.getString(DBConstants.COL_PROGENCY_TAGNO));
			_progencyDto.setBreed(_rsSet.getString(DBConstants.COL_PROGENCY_BREED));
			_progencyDto.setNoOfCalving(_rsSet.getString(DBConstants.COL_PROGENCY_CALVING_NUM));
			_progencyDto.setNoOfAI(_rsSet.getString(DBConstants.COL_PROGENCY_AI_NUM));
			_progencyDto.setPreviousYield(_rsSet.getString(DBConstants.COL_PROGENCY_PREVIOUS_YIELD));
			_progencyDto.setPresentYield(_rsSet.getString(DBConstants.COL_PROGENCY_PRESENT_YIELD));
			_progencyDto.setBullNo(_rsSet.getString(DBConstants.COL_PROGENCY_BULLNO));
			//_progencyDto.setBirthWeight(_rsSet.getString(DBConstants.COL_PROGENCY_BIRTH_WEIGHT));
			//_progencyDto.setMonthlyWeight(_rsSet.getString(DBConstants.COL_PROGENCY_MONTHLY_WEIGHT));
			_progencyDto.setDmMilkYield(_rsSet.getString(DBConstants.COL_PROGENCY_DM_MILK));
			_progencyDto.setSirenBreed(_rsSet.getString(DBConstants.COL_PROGENCY_SIRE_BREED));
			//_progencyDto.setOthers(_rsSet.getString(DBConstants.COL_PROGENCY_OTHER));
			//_progencyDto.setNutrition(_rsSet.getString(DBConstants.COL_PROGENCY_NUTRITION));

			_progencyDto.setFollowUpDetails(getProgencyFollowUps(_progencyDto.getPrgyId()));

			//add the current Object to the list
			_progencyList.add(_progencyDto);
		}

		return _progencyList;
	}//end populateProgencies

	/**
	 * Method called to get Progency Follow Ups
	 */
	private List<FollowUpDetailsDto> getProgencyFollowUps(final long _progencyId) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rstSet = null;
		List<FollowUpDetailsDto> _progencyFollowUpList = null;
		FollowUpDetailsDto followUpDto = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_PRGYFOLLWUP_ON_PRGYID);
			pStmt.setLong(1, _progencyId);
			rstSet = pStmt.executeQuery();

			while(rstSet.next()){
				if(rstSet.isFirst()){
					_progencyFollowUpList = new ArrayList<FollowUpDetailsDto>(1);
				}

				followUpDto = new FollowUpDetailsDto();
				followUpDto.setFollowUpId(rstSet.getLong(DBConstants.COL_PROGENCY_FOLLOWUP_ID));
				followUpDto.setDeWormingDate(rstSet.getDate(DBConstants.COL_PROGENCY_FOLLOWUP_DEWORMING_DATE));
				followUpDto.setVaccinationDate(rstSet.getDate(DBConstants.COL_PROGENCY_FOLLOWUP_VACCINATION_DATE));
				followUpDto.setDeTickingDate(rstSet.getDate(DBConstants.COL_PROGENCY_FOLLOWUP_DETICKING_DATE));
				followUpDto.setOther(rstSet.getString(DBConstants.COL_PROGENCY_FOLLOWUP_OTHER));
				followUpDto.setNutrition(rstSet.getString(DBConstants.COL_PROGENCY_FOLLOWUP_NUTRITION));
				followUpDto.setBirthWeight(rstSet.getString(DBConstants.COL_PROGENCY_FOLLOWUP_BIRTH_WEIGHT));
				followUpDto.setMonthlyWeight(rstSet.getString(DBConstants.COL_PROGENCY_FOLLOWUP_MONTHLY_WEIGHT));

				//add the current Object to the list
				_progencyFollowUpList.add(followUpDto);				
			}//end while

		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _progencyFollowUpList;
	}//end getProgencyFollowUps


	/**
	 * Method is called to search the Progencies based on the gopalamithra.
	 */
	@Override
	public List<ProgencyDto> searchProgencies(final ProgencyDto _progencyDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<ProgencyDto> _progencyList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_PROGENCY);
			if(_progencyDto.getPrgyId() > 0){
				pStmt.setLong(1, _progencyDto.getPrgyId());
				pStmt.setLong(2, _progencyDto.getPrgyId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}

			if(_progencyDto.getCode() != null && !_progencyDto.getCode().trim().equals("")){
				pStmt.setString(3, _progencyDto.getCode() + "%");
			}
			else{
				pStmt.setString(3,"%");
			}

			if(_progencyDto.getFarmerCode() != null && !_progencyDto.getFarmerCode().trim().equals("")){
				pStmt.setString(4, _progencyDto.getFarmerCode() + "%");
			}
			else{
				pStmt.setString(4,"%");
			}

			if(_progencyDto.getFarmerName() != null && !_progencyDto.getFarmerName().trim().equals("")){
				pStmt.setString(5, _progencyDto.getFarmerName() + "%");
			}
			else{
				pStmt.setString(5,"%");
			}

			if(_progencyDto.getBreedType() != null && !_progencyDto.getBreedType().trim().equals("")){
				pStmt.setString(6, _progencyDto.getBreedType() + "%");
			}
			else{
				pStmt.setString(6,"%");
			}

			if(_progencyDto.getBullNo() != null && !_progencyDto.getBullNo().trim().equals("")){
				pStmt.setString(7, _progencyDto.getBullNo() + "%");
			}
			else{
				pStmt.setString(7,"%");
			}
			rsSet = pStmt.executeQuery();

			_progencyList = populateProgencies(rsSet);
		} catch (Exception e) {
			//throw e;
			e.printStackTrace();
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _progencyList;
	}//end searchProgencies
	
	/**
	 *Method called to Delete a Follow Details of the Progency
	 */
	@Override
	public void deleteFollowUpProgency(final long _followUpProgencyId) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.DELETE_PRGYFOLLWUP);
			pStmt.setLong(1, _followUpProgencyId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

	}//end deleteDept

}//end class
